﻿
CREATE PROCEDURE [temp].[s_Vantage_ExportNotes] AS

--SELECT MAX(NoteDate) FROM t_Note WHERE StatusID = dbo.fs_StateID__Notes__FromVantage()

DROP TABLE temp.t_Note_Vantage
SELECT SUBSTRING(LoanID,3,8)UsixID,CASE LEFT(LoanID,2)WHEN '0A' THEN 1 WHEN '0B' THEN 2 ELSE 0 END CompanyID,NoteDate,UserID,Note
INTO temp.t_Note_Vantage
  FROM [MotoBank].[dbo].[Query]
WHERE USerID <> 'sa' AND (LoanID LIKE '0B%' OR LoanID LIKE '0A%') AND NoteDate > '7/9/2008'
--GROUP BY LEFT(LoanID,2),SUBSTRING(LoanID,3,2)
ORDER BY NoteDate

INSERT INTO t_Note(
LoanID		 ,Note		,StatusID														 ,StatusDate	,StatusUser,NoteTypeID,NoteActionCodeID,DateDueBy
)
SELECT
CF_E.LoanID,N_V.Note,dbo.fs_StateID__Notes__FromVantage(),N_V.NoteDate,N_V.UserID,1,1,N_V.UserID
FROM         temp.t_Note_Vantage AS N_V INNER JOIN
                      usix.t_CustomerFile_Export AS CF_E ON N_V.UsixID = CF_E.UsixID AND N_V.CompanyID = CF_E.CompanyID_Raw
WHERE CF_E.LoanID > 0
ORDER BY N_V.NoteDate

--SELECT * FROM Query WHERE NoteDate BETWEEN '7/6/08' AND '7/6/08' ORDER BY NoteDate

